"""
    本文档用于 configData 配置文件的SQL查询
"""
from common.constant import TEST_DEPARTMENT_ID

department_person_info_list_sql = f"""
select 
	usr.id, 
	usr.account as account, 
	dpt.name as dpt_name, 
	usr.realname as tester_name,
	grp.`name` as tester_role
from 
	zt_user as usr 
LEFT JOIN
	zt_dept as dpt 
on 
	dpt.id=usr.dept 
LEFT JOIN
	zt_group as grp 
ON
	grp.role = usr.role
WHERE
	usr.deleted = '0'
and
	SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
"""


def construct_department_person_update_sql(search_list=[]):
    return f"""
select 
	usr.id, 
	usr.account as pingying, 
	dpt.name as dpt_name, 
	usr.realname as tester_name
from 
	zt_user as usr 
LEFT JOIN
	zt_dept as dpt 
on 
	dpt.id=usr.dept 
WHERE
    usr.deleted = '0'
{
    f'''and 
            usr.realname in {tuple(search_list)}''' if len(search_list) > 1 else 
    f'''and 
            usr.realname = "{search_list[0]}"''' if len(search_list) == 1 else ''
}
"""

department_info_sql = """
select 
	id as dept_id,
	name as dept_name
from 
	zt_dept as dept
"""

person_info_list_sql = """
select 
	id as user_id,
	realname,
	account
from 
	zt_user as usr
"""


person_role_info_list_sql = """
select 
	id, 
	name,
	role,
	`desc` as description
from 
	zt_group
"""


account_name_list_sql = """
select 
	id as user_id,
	realname,
	account
from 
	zt_user as usr
"""














